<?php
namespace Home\Controller;
use Think\Controller;
use Home\Event\LetterEvent;
//set_time_limit(100);


class UserBaseController extends Controller {
    public function __construct(){
        parent::__construct();
    }

    /**
     *判断是否已经验证过手机
     */
    public function index(){
        
        $this->display();
    }

    /**
     * 会员统计
     * $time : 1-统计前一天的、0-统计全部
     */
    public function userStatisticsDay(){
        $log_data = [
            'name' => '会员月度统计',
            'log_status' => '1',
            'run_msg' => '会员月度统计',
            'run_time' => time(),
        ];
        M('statistical_log')->add($log_data);
        $where = ' and 1=1';
        //判断是否是首次执行
       if(M('statistical_member_day')->select()){
           $starDate = strtotime(date('Y-m-d',time()-86400));
           $endDate = $starDate + 86399;
           $where = "AND `time` >= {$starDate} AND `time` < {$endDate}";
       }

        //dump($where);die;
        if($where == ' and 1=1'){
            M()->query("DELETE FROM sdb_statistical_member_day");
        }else{
            M('statistical_member_day')->where(["date" => date('Y-m-d',time()-86400)])->delete();
        }
        //dump($where);
        $obj = M();
        //支付成功的
        $sql1 = "SELECT m.`member_id`,bile.local_id,m.nickname,IF(m.`mobile`,m.`mobile`,0) AS mobile,
                ma.name AS defualt_name,IF(ma.`mobile`,ma.`mobile`,0) AS defualt_phone
                ,IF(SUM(bile.num), SUM(bile.num), 0) AS buy_num,IF(SUM(bile.`money`),SUM(bile.`money`),0.00) AS money
                ,IF(bile.`time`,bile.`time`,'0000-00-00') AS `date`,bile.bill_type
                FROM `sdb_b2c_members` m
                INNER JOIN (
                (SELECT id,member_id,local_id,FROM_UNIXTIME(`time`,'%Y-%m-%d') AS `time`,bill_type,COUNT(id) AS num,SUM(`money`) AS money FROM `sdb_b2c_bills`
                WHERE bill_type='payments' {$where} GROUP BY member_id,local_id,FROM_UNIXTIME(`time`,'%Y-%m-%d'))

                ) bile ON m.`member_id`=bile.member_id
                LEFT JOIN sdb_b2c_member_addrs ma ON m.`member_id`=ma.`member_id` AND ma.`def_addr`=1
                 GROUP BY m.`member_id`,bile.local_id,bile.bill_type ,bile.time";
        //echo $sql1;die;
        $pay_data = $obj->query($sql1);
        //dump($pay_data);
        //退款的
        $sql2 = "SELECT m.`member_id`,bile.local_id,COUNT(bile.id) AS refund_num,SUM(bile.`money`) AS refund_money,bile.bill_type,
                FROM_UNIXTIME(bile.`time`,'%Y-%m-%d') AS `date` FROM `sdb_b2c_members` m
                INNER JOIN sdb_b2c_bills bile ON m.`member_id`=bile.`member_id` AND bile.`bill_type`='refunds'  {$where}
                 GROUP BY m.`member_id`,bile.local_id,FROM_UNIXTIME(`time`,'%Y-%m-%d')";
        $refund_data = $obj->query($sql2);
        //echo '<br/>'.$sql2;die;
        //dump($refund_data);
        $data = [];
        $r_data = [];
        foreach($pay_data as $k1 => $v1){
            $data[$k1] = $v1;
            $data[$k1]['refund_num'] = 0;
            $data[$k1]['refund_money'] = 0.00;
            $data[$k1]['time'] = strtotime($v1['date']);
            foreach($refund_data as $k2 => $v2){
                if($v1['member_id'] == $v2['member_id'] && $v1['local_id'] == $v2['local_id']){
                    $r_data[$k2]['member_id'] = $v2['member_id'];
                    $r_data[$k2]['local_id'] = $v2['local_id'];
                    $r_data[$k2]['nickname'] = $v1['nickname'];
                    $r_data[$k2]['mobile'] = $v1['mobile'];
                    $r_data[$k2]['defualt_name'] = $v1['defualt_name'];
                    $r_data[$k2]['defualt_phone'] = $v1['defualt_phone'];
                    $r_data[$k2]['buy_num'] = 0;
                    $r_data[$k2]['money'] = 0.00;
                    $r_data[$k2]['date'] = $v2['date'];
                    $r_data[$k2]['refund_num'] = $v2['refund_num'];
                    $r_data[$k2]['refund_money'] = $v2['refund_money'];
                    $r_data[$k2]['time'] = strtotime($v2['date']);
                    $r_data[$k2]['average_order'] = 0.00;
                    $r_data[$k2]['created_at'] = date('Y-m-d H:i:s');
                    //时间相等的时候
                    if($v1['date'] == $v2['date']){
                        $data[$k1]['refund_num'] = $v2['refund_num'];
                        $data[$k1]['refund_money'] = $v2['refund_money'];
                        if($v1['bill_type'] == 'refunds'){
                            $data[$k1]['buy_num'] = 0;
                            $data[$k1]['money'] = 0.00;
                        }
                        //时间相等的时候就销毁退款数据，最后没销毁的再压入data
                        unset($r_data[$k2]);
                    }
                    unset($refund_data[$k2]['bill_type']);
                }
            }
            //均订单价=（ 消费总金额 - 退款总金额 )÷ 购买次数
            $pay_data[$k1]['average_order'] = 0.00;
            if((float)$pay_data[$k1]['money'] !== 0.00){
                $data[$k1]['average_order'] = round(bcdiv(($pay_data[$k1]['money'] - $pay_data[$k1]['refund_money']), $pay_data[$k1]['buy_num'], 3),2);
            }
            $data[$k1]['created_at'] = date('Y-m-d H:i:s');
            unset($data[$k1]['bill_type']);
        }
        //dump($r_data);
        foreach($r_data as $k3 => $v3){
            array_push($data,$v3);
        }
        //dump($data);die;


        $error_logTxt = $_SERVER['DOCUMENT_ROOT']."/Datalog/user/".date('Y-m-d')."_user.txt";
        if($data){
            $status = M('statistical_member_day')->addAll($data);
            //echo M()->_sql();
            //dump($status);
            if($status === false){
               $errormsg = M('statistical_member_day')->getDbError();
                //dump($errormsg);
                $msg = date('Y-m-d H:i:s').' 用户统计(天)错误，错误码：'.$errormsg;
                $this->addText($error_logTxt,$msg);
                $log_data = [
                    'name' => '会员统计（天）',
                    'log_status' => '1',
                    'run_msg' => $msg,
                    'run_time' => time(),
                ];
            }else{
                $log_data = [
                    'name' => '会员统计（天）',
                    'log_status' => '1',
                    'run_msg' => '会员统计（天）成功，影响行数：'.$status,
                    'run_time' => time(),
                ];
            }
        }else{
            $msg = date('Y-m-d H:i:s').' 用户统计(天)错误，错误原因：'.date('Y-m-d',time()-86400).'没有数据';
            $this->addText($error_logTxt,$msg);
            $log_data = [
                'name' => '会员统计（天）',
                'log_status' => '1',
                'run_msg' => $msg,
                'run_time' => time(),
            ];
        }

        M('statistical_log')->add($log_data);

    }

    public function userStatisticsMonth(){
        $log_data = [
            'name' => '会员月度统计',
            'log_status' => '1',
            'run_msg' => '会员月度统计',
            'run_time' => time(),
        ];
        M('statistical_log')->add($log_data);
        $where = '1=1';
        //判断是否是首次执行
        if(M('statistical_member_mouth')->select()){
            $y = date('Y');
            $m = date('m')-1;
            if(strlen($m) <= 1){
                $m = '0'.$m;
            }
            $date = $y.'-'.$m;
            $day = date("t",strtotime($date));
            $where = " `date` >= '".$date."-01' AND `date` < '".$date.'-'.$day."'";
        }

        if($where == '1=1'){
            M()->query("DELETE FROM sdb_statistical_member_mouth");
        }else{
            M('statistical_member_mouth')->where(['date' => date('Y-m')])->delete();
        }

        //echo M()->_sql();die;
        $obj = M();
        $sql1 = "SELECT member_id,local_id,mobile,nickname,defualt_name,defualt_phone,SUM(buy_num) AS buy_num,SUM(money) AS money,
                    SUM(refund_num) AS refund_num,SUM(refund_money) AS refund_money,FROM_UNIXTIME(`time`,'%Y-%m') AS `date`
                 FROM `sdb_statistical_member_day`  WHERE {$where}
                 GROUP BY local_id,member_id,FROM_UNIXTIME(`time`,'%Y-%m')";
        $data = $obj->query($sql1);
        //echo $sql1;
        foreach($data as $k1 => $v1){
            //均订单价=（ 消费总金额 - 退款总金额 )÷ 购买次数
            $data[$k1]['average_order'] = 0.00;
            if((float)$data[$k1]['money'] !== 0.00){
                $data[$k1]['average_order'] = round(bcdiv(($data[$k1]['money'] - $data[$k1]['refund_money']), $data[$k1]['buy_num'], 3),2);
            }
            $data[$k1]['created_at'] = date('Y-m-d H:i:s');
        }
        //dump($data);
        $error_logTxt = $_SERVER['DOCUMENT_ROOT']."/Datalog/user/".date('Y-m-d')."_user.txt";
        if($data){
            $status = M('statistical_member_mouth')->addAll($data);
            if($status === false){
                $errormsg = M('statistical_member_day')->getDbError();
                //dump($errormsg);
                $msg = date('Y-m-d H:i:s').' 用户统计(月)错误，错误码：'.$errormsg;
                $this->addText($error_logTxt,$msg);
                $log_data = [
                    'name' => '会员月度统计',
                    'log_status' => '2',
                    'run_msg' => $msg,
                    'run_time' => time(),
                ];
            }else{
                $log_data = [
                    'name' => '会员月度统计',
                    'log_status' => '1',
                    'run_msg' => '会员月度统计成功，影响行数：'.$status,
                    'run_time' => time(),
                ];
            }
        }else{
            $msg = date('Y-m-d H:i:s').' 用户统计(月)错误，错误原因：'.$date.'没有数据';
            $this->addText($error_logTxt,$msg);
            $log_data = [
                'name' => '会员月度统计',
                'log_status' => '2',
                'run_msg' => $msg,
                'run_time' => time(),
            ];
        }
        M('statistical_log')->add($log_data);

    }

    /**
     * 新增会员统计
     */
    public function userStatisticsNew(){
        $log_data = [
            'name' => '新增会员统计',
            'log_status' => '1',
            'run_msg' => '新增会员统计',
            'run_time' => time(),
        ];
        M('statistical_log')->add($log_data);
        $where = '1=1';
        //判断是否是首次执行
        if(M('statistical_member_new')->select()){
            $starDate = strtotime(date('Y-m-d',time()-86400));
            $endDate = $starDate + 86399;
            $where = " `create_time` >= {$starDate} AND `create_time` < {$endDate}";
        }

        if($where == '1=1'){
            M()->query("DELETE FROM sdb_statistical_member_new");
        }else{
            $date_new = date('Y-m-d',time()-86400);
            M('statistical_member_new')->where(['date' => $date_new])->delete();
        }

        $sql1 = "select a.local_id,if(a.create_time,a.create_time,'0000-00-00') as `date`,count(a.local_id) num,sum(a.gm) xf_num from
                (SELECT su1.local_id,su1.member_id,FROM_UNIXTIME(`create_time`,'%Y-%m-%d') AS create_time,if(o.`order_id`,1,0) as gm FROM `sdb_b2c_store_user` as su1
                    left join sdb_b2c_orders as o on su1.`local_id`=o.`local_id` AND su1.`member_id`=o.`member_id` AND o.`pay_status`='1'
                    and FROM_UNIXTIME(su1.`create_time`,'%Y-%m-%d')=FROM_UNIXTIME(o.`createtime`,'%Y-%m-%d') WHERE {$where}
                group by su1.local_id,su1.member_id) a
        group by a.local_id,a.create_time";
        //echo $sql1;
        $data = M()->query($sql1);
        foreach($data as $k1 => $v1){
            $data[$k1]['created_at'] = date('Y-m-d H:i:s');
            $data[$k1]['num'] = (int)$data[$k1]['num'];
        }
        //dump($data);die;
        $error_logTxt = $_SERVER['DOCUMENT_ROOT']."/Datalog/user/".date('Y-m-d')."_user.txt";
        if($data){
            $status = M('statistical_member_new')->addAll($data);
            if($status === false){
                $errormsg = M('statistical_member_day')->getDbError();
                //dump($errormsg);
                $msg = date('Y-m-d H:i:s').' 新增会员统计错误，错误码：'.$errormsg;
                $this->addText($error_logTxt,$msg);
                $log_data = [
                    'name' => '新增会员统计',
                    'log_status' => '2',
                    'run_msg' => $msg,
                    'run_time' => time(),
                ];
            }else{
                $log_data = [
                    'name' => '新增会员统计',
                    'log_status' => '1',
                    'run_msg' => '新增会员统计成功,受影响行数：'.$status,
                    'run_time' => time(),
                ];
            }
        }else{
            $msg = date('Y-m-d H:i:s').' 新增会员统计错误，错误原因：'.$date_new.'没有数据';
            $this->addText($error_logTxt,$msg);
            $log_data = [
                'name' => '新增会员统计',
                'log_status' => '2',
                'run_msg' => $msg,
                'run_time' => time(),
            ];
        }
        M('statistical_log')->add($log_data);
    }
    /**
     * @param $filename 文件名
     * @param $content  写入内容
     */
    public function addText($filename,$content){
        $filePath= $filename;
        $handle=fopen($filePath,"a+");

        $str=fwrite($handle,$content."\r\n");

        fclose($handle);
    }


   public function __destruct(){
       parent::__destruct();
       unset($this->obj);
   }


    

   

}